from datetime import datetime, timedelta

from django.db import connection
from django.db.models import Count
from django.utils.timezone import utc
from django_redis import get_redis_connection
from collections import namedtuple

from .models import Post, Category


# 获取两周内浏览量最多的五篇文章
# Post模型中pub_date存储的是带时区的UTC时间(timezone.now())
# 所以这里不使用datetime.utcnow()
def _get_popular_posts():
    today = datetime.utcnow().replace(tzinfo=utc)
    two_weeks_ago = today - timedelta(days=14)
    two_weeks_posts = Post.objects.filter(date_created__gt=two_weeks_ago)
    popular_posts = two_weeks_posts.order_by("-num_views")[:3]
    return popular_posts


# 显示用户文章分类及该分类下文章的数目
# 按照目录进行分组，获得该目录下文章数目及类别名称，其中文章数目不能为0
# < QuerySet[{'name': 'Python', 'post_count': 1}, {'name': 'Django', 'post_count': 2}>
def _get_category_with_post_count(request):
    category_with_post_count = (
        Category.objects.filter(user=request.user)
            .annotate(post_count=Count("post__id"))
            .values("name", "post_count")
            .filter(post_count__gt=0)
    )
    return category_with_post_count


# 显示用户的文章归档，按年月分类，并统计该年月下文章数量
def _get_month_archives(request):
    user_id = request.user.id

    # 先按文章个数排序后按时间降序排
    sql = f"""
SELECT date_format(date_created, '%Y-%m') AS df, count(id) AS post_count FROM blog_post WHERE user_id = {user_id} \
GROUP BY df ORDER BY post_count, df DESC; 
"""
    with connection.cursor() as cur:
        cur.execute(sql)
        results = (
            cur.fetchall()
        )  # [('2020-04', 1), ('2020-05', 2), ('2020-06', 1), ('2030-05', 1)]

    # 使用命名元组使用模板访问属性语义话,如item.year_month而不是item.0
    MonthArchive = namedtuple('MonthArchive', ('year_month', 'post_count'))
    post_archives = [MonthArchive(ym, pc) for ym, pc in results]
    return post_archives


# 导航栏目
def _get_nav_categories():
    return Category.objects.filter(is_nav=True)[:5]


# 从redis中获取最近浏览记录
def _get_recent_visited_posts(request):
    conn = get_redis_connection("default")
    visited_key = "visited_%d" % request.user.id
    slug_list = conn.lrange(visited_key, 0, 4)  # [b'NeAhKYK4SMbD'] # 需要解码

    recent_visited_posts = []
    for slug in slug_list:
        try:
            post = Post.objects.get(slug=slug.decode())
        except Post.DoesNotExist:
            continue
        else:
            recent_visited_posts.append(post)
    return recent_visited_posts


def blog(request):
    context = dict()
    context['popular_posts'] = _get_popular_posts()

    if hasattr(request, "user") and request.user.is_authenticated:
        context['category_with_post_count'] = _get_category_with_post_count(request)

        context['post_archives'] = _get_month_archives(request)

        context['nav_categories'] = _get_nav_categories()

        context['recent_visited_posts'] = _get_recent_visited_posts(request)

    return context
